/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import ConnectDatabasse.ConnectionData;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author User
 */
public class AccessDatabase {
    private static final String QUERY_STR = "SELECT * FROM Khachhang";
    private static final String QUERY_ADD = "INSERT INTO Khachhang(CustomerID,CustomerName,Birth,PhoneNumber,Address,MaPin,Sothe,Sotaikhoan) VALUES(?,?,?,?,?,?,?,?)";
    private static final String QUERY_UPDATE = "UPDATE Khachhang SET CustomerName=?,CustomerID=?,Address=?,Birth=?,PhoneNumber=?,Sotaikhoan=?,Sothe=?,MaPin=? WHERE CustomerName=?";
    private static final String QUERY_SEARCH = "SELECT * FROM Khachhang where CustomerName like ?";

    Connection con = null;
    ConnectionData Connect = new ConnectionData();

    public List<Customer> getCustomer() throws Exception {
        con = Connect.getConnect();
        List<Customer> std = new ArrayList<Customer>();

        PreparedStatement ps = con.prepareStatement(QUERY_STR);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {

            String CustomerName = rs.getString("CustomerName");
            String CustomerID = rs.getString("CustomerID");
            String Address = rs.getString("Address");
            String Birth = rs.getString("Birth");
            String PhoneNumber = rs.getString("PhoneNumber");
            String Sotaikhoan = rs.getString("Sotaikhoan");
            String Sothe = rs.getString("Sothe");
            String MaPin = rs.getString("MaPin");
            std.add(new Customer(CustomerName,CustomerID,Address,Birth,PhoneNumber,Sotaikhoan,Sothe,MaPin));
        }
        con.close();

        return std;
    }
    public void UpdateCustomer(Customer c) throws Exception {
        con = Connect.getConnect();
        PreparedStatement ps = con.prepareStatement(QUERY_UPDATE);
        ps.setString(1, c.getCustomerName());
        ps.setString(2, c.getCustomerID());
        ps.setString(3, c.getAddress());
        ps.setString(4, c.getBirth());
        ps.setString(5, c.getPhoneNumber());
        ps.setString(5, c.getSotaikhoan());
        ps.setString(7, c.getSothe());
        ps.setString(8, c.getMaPin());
        ps.executeUpdate();
        con.close();
    }

    public void AddCustomer(Customer c) throws Exception {
        con = Connect.getConnect();
        PreparedStatement ps = con.prepareStatement(QUERY_ADD);
        ps.setString(1, c.getCustomerName());
        ps.setString(2, c.getCustomerID());
        ps.setString(3, c.getAddress());
        ps.setString(4, c.getBirth());
        ps.setString(5, c.getPhoneNumber());
        ps.setString(5, c.getSotaikhoan());
        ps.setString(7, c.getSothe());
        ps.setString(8, c.getMaPin());
        ps.executeUpdate();
        con.close();
    }
    public void DeleteCustomer(Customer c) throws Exception {
        con = Connect.getConnect();
        PreparedStatement ps = con.prepareStatement("DELETE Student where CustomerName=?");
        ps.setString(1, c.getCustomerName());
        ps.executeUpdate();
        con.close();
    }
     public List<Customer> SearchCustomer(String name) throws Exception {
        con = Connect.getConnect();
        List<Customer> std = new ArrayList<Customer>();
        PreparedStatement ps = con.prepareStatement(QUERY_SEARCH);
        ps.setString(1, name + "%");

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {

            String CustomerName = rs.getString("CustomerName");
            String CustomerID = rs.getString("CustomerID");
            String Address = rs.getString("Address");
            String Birth = rs.getString("Birth");
            String PhoneNumber = rs.getString("PhoneNumber");
            String Sotaikhoan = rs.getString("Sotaikhoan");
            String Sothe = rs.getString("Sothe");
            String MaPin = rs.getString("MaPin");
            std.add(new Customer(CustomerName,CustomerID,Address,Birth,PhoneNumber,Sotaikhoan,Sothe,MaPin));
        }
        return std;
     }
}
